<?php

/**
 * @version     1.0.5
 * @package     com_vereadores
 * @copyright   Copyright (C) 2013. All rights reserved.
 * @license     GNU General Public License version 2 or later; see LICENSE.txt
 * @author      JORGE EDUARDO DE O LIVEIRA LUCIANO <jeolcavaco@hotmail.com> - http://jorgeeduardo.wordpress.com
 */
defined('_JEXEC') or die;

jimport('joomla.application.component.modellist');

/**
 * Methods supporting a list of Vereadores records.
 */
class GaneshaVereadoresModelProposicoes extends JModelList {

    /**
     * Constructor.
     *
     * @param    array    An optional associative array of configuration settings.
     * @see        JController
     * @since    1.6
     */
    public function __construct($config = array()) {
        parent::__construct($config);
    }

    /**
     * Method to auto-populate the model state.
     *
     * Note. Calling getState in this method will result in recursion.
     *
     * @since	1.6
     */
    protected function populateState($ordering = null, $direction = null) {

        // Initialise variables.
        $app = JFactory::getApplication();

        // List state information
   
      
		$limit = $app->getUserStateFromRequest('global.list.limit', 'limit', $app->getCfg('list_limit'), 'uint');
		$this->setState('list.limit',$limit);
		$this->setState('list.start', $app->input->get('limitstart', 0, 'uint'));
        
		if(empty($ordering)) {
			$ordering = 'a.ordering';
		}
			
		
				
		$this->setState('filter.vereador',  $app->input->getString('filter-vereador'));
		$this->setState('filter.categoria',  $app->input->getString('filter-categoria'));
		$this->setState('filter.ano',  $app->input->getString('filter-ano'));
				
        // List state information.
        parent::populateState($ordering, $direction);
    }

    /**
     * Build an SQL query to load the list data.
     *
     * @return	JDatabaseQuery
     * @since	1.6
     */
    protected function getListQuery() {
        // Create a new query object.
        $db = $this->getDbo();
        $query = $db->getQuery(true);

        // Select the required fields from the table.
        $query->select(
                $this->getState(
                        'list.select', 'a.*'
                )
        );

        $query->from('`#__ganeshavereadores_proposicoes` AS a');
        $query->select('ca.nome AS catalias');
        $query->join('LEFT', '#__ganeshavereadores_categoria AS ca ON ca.id=a.categoria');
        
	    // Join over the users for the checked out user.
	    $query->select('uc.name AS editor');
	    $query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out');
    
		// Join over the created by field 'created_by'
		$query->select('created_by.name AS created_by');
		$query->join('LEFT', '#__users AS created_by ON created_by.id = a.created_by');
        $query->order('a.numero Desc');
        
        if($this->getState('filter.vereador')){        	        	
        	$query->where('( a.vereador = '.$this->getState('filter.vereador').' )');
        }else{
        	$query->where('( a.vereador = 0 )');
        }
        
        if($this->getState('filter.categoria')){
        	$query->where('( a.categoria = '.$this->getState('filter.categoria').' )');
        }

        if($this->getState('filter.ano') != ' Selecione um Ano ' && ($this->getState('filter.ano')!='')){
        	$query->where('( a.ano = '.$this->getState('filter.ano').' )');
        }else{
        	$query->where('( a.ano = 0 )');
        }
        
        // Filter by search in title
        $search = $this->getState('filter.search');
        if (!empty($search)) {
            if (stripos($search, 'id:') === 0) {
                $query->where('a.id = ' . (int) substr($search, 3));
            } else {
                $search = $db->Quote('%' . $db->escape($search, true) . '%');
                $query->where('( a.titulo LIKE '.$search.' )');
            }
        }
        
        return $query;
    }

    public function getItems() {
        return parent::getItems();
    }
    
    
    

}
